/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2012/9/11 16:16:23                           */
/*==============================================================*/
use TWCRM
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Address') and o.name = 'FK_ADDRESS_REFERENCE_CUSTOMER')
alter table Address
   drop constraint FK_ADDRESS_REFERENCE_CUSTOMER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EMail') and o.name = 'FK_EMAIL_REFERENCE_CUSTOMER')
alter table EMail
   drop constraint FK_EMAIL_REFERENCE_CUSTOMER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_REFERENCE_CUSTOMER')
alter table "Order"
   drop constraint FK_ORDER_REFERENCE_CUSTOMER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderDetail') and o.name = 'FK_ORDERDET_REFERENCE_ORDER')
alter table OrderDetail
   drop constraint FK_ORDERDET_REFERENCE_ORDER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderDetail') and o.name = 'FK_ORDERDET_REFERENCE_PRODUCT')
alter table OrderDetail
   drop constraint FK_ORDERDET_REFERENCE_PRODUCT
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Phone') and o.name = 'FK_PHONE_REFERENCE_CUSTOMER')
alter table Phone
   drop constraint FK_PHONE_REFERENCE_CUSTOMER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PRODUCT_REFERENCE_PRODUCTT')
alter table Product
   drop constraint FK_PRODUCT_REFERENCE_PRODUCTT
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ProductType') and o.name = 'FK_PRODUCTT_REFERENCE_PRODUCTT')
alter table ProductType
   drop constraint FK_PRODUCTT_REFERENCE_PRODUCTT
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserAuthority') and o.name = 'FK_USERAUTH_REFERENCE_USER')
alter table UserAuthority
   drop constraint FK_USERAUTH_REFERENCE_USER
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Address')
            and   type = 'U')
   drop table Address
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Customer')
            and   type = 'U')
   drop table Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EMail')
            and   type = 'U')
   drop table EMail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Order"')
            and   type = 'U')
   drop table "Order"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OrderDetail')
            and   type = 'U')
   drop table OrderDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Phone')
            and   type = 'U')
   drop table Phone
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Product')
            and   type = 'U')
   drop table Product
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductType')
            and   type = 'U')
   drop table ProductType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"User"')
            and   type = 'U')
   drop table "User"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserAuthority')
            and   type = 'U')
   drop table UserAuthority
go

/*==============================================================*/
/* Table: Address                                               */
/*==============================================================*/
create table Address (
   ID                   uniqueidentifier     not null,
   Type                 varchar(20)          not null,
   Country              varchar(50)          not null,
   Province             varchar(50)          not null,
   City                 varchar(50)          not null,
   Streat               varchar(500)         not null,
   Zip                  varchar(50)          not null,
   CustomerID           uniqueidentifier     not null,
   Seq                  int                  not null,
   constraint PK_ADDRESS primary key (ID)
)
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   ID                   uniqueidentifier     not null,
   FirstName            varchar(50)          not null,
   LastName             varchar(50)          not null,
   Company              varchar(200)         not null,
   Department           varchar(50)          not null,
   Position             varchar(50)          not null,
   Memo                 ntext                not null,
   constraint PK_CUSTOMER primary key (ID)
)
go

/*==============================================================*/
/* Table: EMail                                                 */
/*==============================================================*/
create table EMail (
   ID                   uniqueidentifier     not null,
   Type                 varchar(20)          not null,
   EMail                varchar(50)          not null,
   CustomerID           uniqueidentifier     not null,
   Seq                  int                  not null,
   constraint PK_EMAIL primary key (ID)
)
go

/*==============================================================*/
/* Table: "Order"                                               */
/*==============================================================*/
create table "Order" (
   ID                   uniqueidentifier     not null,
   OrderDate            datetime             not null,
   Date                 datetime             not null,
   CustomerID           uniqueidentifier     not null,
   TotalPrice           decimal(10,2)        not null,
   constraint PK_ORDER primary key (ID)
)
go

/*==============================================================*/
/* Table: OrderDetail                                           */
/*==============================================================*/
create table OrderDetail (
   ID                   uniqueidentifier     not null,
   OrderID              uniqueidentifier     not null,
   ProductID            uniqueidentifier     null,
   ProductType          varchar(500)         not null,
   ProductName          varchar(50)          not null,
   ProductBrand         varchar(50)          not null,
   ProductModel         varchar(50)          not null,
   Price                decimal(10,2)        not null,
   Qty                  decimal(10,2)        not null,
   TotalPrice           decimal(10,2)        not null,
   constraint PK_ORDERDETAIL primary key (ID)
)
go

/*==============================================================*/
/* Table: Phone                                                 */
/*==============================================================*/
create table Phone (
   ID                   uniqueidentifier     not null,
   Type                 varchar(20)          not null,
   Phone                varchar(50)          not null,
   CustomerID           uniqueidentifier     not null,
   Seq                  char(10)             null,
   constraint PK_PHONE primary key (ID)
)
go

/*==============================================================*/
/* Table: Product                                               */
/*==============================================================*/
create table Product (
   ID                   uniqueidentifier     not null,
   Name                 varchar(50)          not null,
   Brand                varchar(50)          not null,
   Model                varchar(50)          not null,
   ListPrice            decimal(10,2)        not null,
   ProductTypeID        uniqueidentifier     not null,
   constraint PK_PRODUCT primary key (ID)
)
go

/*==============================================================*/
/* Table: ProductType                                           */
/*==============================================================*/
create table ProductType (
   ID                   uniqueidentifier     not null,
   ParentID             uniqueidentifier     null,
   Name                 varchar(50)          not null,
   FullName             varchar(500)         not null,
   constraint PK_PRODUCTTYPE primary key (ID)
)
go

/*==============================================================*/
/* Table: "User"                                                */
/*==============================================================*/
create table "User" (
   ID                   uniqueidentifier     not null,
   Name                 varchar(30)          not null,
   Password             varchar(30)          not null,
   Effective            bit                  not null,
   constraint PK_USER primary key (ID)
)
go

/*==============================================================*/
/* Table: UserAuthority                                         */
/*==============================================================*/
create table UserAuthority (
   ID                   uniqueidentifier     not null,
   Authority            varchar(50)          not null,
   Allow                bit                  not null,
   UserID               uniqueidentifier     not null,
   constraint PK_USERAUTHORITY primary key (ID)
)
go

alter table Address
   add constraint FK_ADDRESS_REFERENCE_CUSTOMER foreign key (CustomerID)
      references Customer (ID)
go

alter table EMail
   add constraint FK_EMAIL_REFERENCE_CUSTOMER foreign key (CustomerID)
      references Customer (ID)
go

alter table "Order"
   add constraint FK_ORDER_REFERENCE_CUSTOMER foreign key (CustomerID)
      references Customer (ID)
go

alter table OrderDetail
   add constraint FK_ORDERDET_REFERENCE_ORDER foreign key (OrderID)
      references "Order" (ID)
go

alter table OrderDetail
   add constraint FK_ORDERDET_REFERENCE_PRODUCT foreign key (ProductID)
      references Product (ID)
go

alter table Phone
   add constraint FK_PHONE_REFERENCE_CUSTOMER foreign key (CustomerID)
      references Customer (ID)
go

alter table Product
   add constraint FK_PRODUCT_REFERENCE_PRODUCTT foreign key (ProductTypeID)
      references ProductType (ID)
go

alter table ProductType
   add constraint FK_PRODUCTT_REFERENCE_PRODUCTT foreign key (ParentID)
      references ProductType (ID)
go

alter table UserAuthority
   add constraint FK_USERAUTH_REFERENCE_USER foreign key (UserID)
      references "User" (ID)
go

